package tech.yunland.shop.domain.service;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import tech.yunland.shop.domain.dao.GoodsDao;
import tech.yunland.shop.domain.dao.GoodsPaidDao;
import tech.yunland.shop.domain.model.Goods;
import tech.yunland.shop.domain.model.GoodsPaid;
import tech.yunland.shop.domain.model.SN;

import com.moyuer.bsf.common.PinYinUtil;
import com.moyuer.bsf.domain.dao.AttachmentDao;
import com.moyuer.bsf.domain.dao.OrganDao;
import com.moyuer.bsf.domain.dao.UserDao;
import com.moyuer.bsf.domain.model.User;

/**
 * Excel导入service
 * 
 * @author 王睿智<geekrize@gmail.com>
 * 
 */
@Service
public class ImportExcelService {

	@Autowired
	private OrganDao organDao;

	@Autowired
	private UserDao userDao;

	@Autowired
	private GoodsDao goodsDao;

	@Autowired
	private GoodsPaidDao goodsPaidDao;

	@Autowired
	private AttachmentDao attachmentDao;

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private SNService sNService;

	@Value("${upload.save.dir}")
	private String uploads_dir;

	/**
	 * 读取上传Excel FileName
	 * 
	 * @return String FileName
	 */
	public String getFileName(String excelId) {

		return attachmentDao.get(excelId).getFileName();

	}

	/**
	 * 解析并导入excel2003
	 * 
	 * @param fileName
	 * @param organId
	 * @param inmport_Type
	 * @return
	 * @throws IOException
	 */
	public void parseHSSFWorkbook(String fileName, String organId,String import_Type, String creator) throws IOException {
		FileInputStream fis = new FileInputStream(uploads_dir + "/" + fileName);
		HSSFWorkbook workBook = new HSSFWorkbook(fis);
		// Read the Sheet 只读取第一页签内容
		HSSFSheet sheet = workBook.getSheetAt(0);
		if (sheet == null) {
		}
		if (import_Type.equals("1")) {
			String sql = " delete from goods_paid where organ_id = '" + organId+ "'  ";
			jdbcTemplate.execute(sql);
		}
		// Read the Row 从第三行开始取参
		for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
			HSSFRow row = sheet.getRow(rowNum);
			if (row != null) {
                 //参数格式化及容错处理
				String userName = this.replaceBlank(this.getValueHSSF(row.getCell(0)));// 会员名称
				String userPhone = this.replaceBlank(this.getValueHSSF(row.getCell(1)));// 电话
				String userEmail = this.replaceBlank(this.getValueHSSF(row.getCell(2)));//邮箱
				String goodsName = this.replaceBlank(this .getValueHSSF(row.getCell(3)));//商品名称
				String strQuantity = this.replaceBlank(this.getValueHSSF(row.getCell(4)));//数量
				String remarks = this.replaceBlank(this.getValueHSSF(row.getCell(5)));//备注
				//参数校验
				this.faultTolerant(userName, goodsName, strQuantity,rowNum);
				// 获取goods对象
				DetachedCriteria Cgoods = DetachedCriteria.forClass(Goods.class);
				Cgoods.add(Restrictions.eq("name", goodsName));
				Cgoods.add(Restrictions.eq("state",1));
				Cgoods.createCriteria("organ").add(Restrictions.idEq(organId));
				List<Goods> goods = goodsDao.findByCriteria(Cgoods);
				if(goods.size()<=0){
					throw new RuntimeException("该商品不存在");
				}
				//格式化数量
				BigDecimal quantity = new BigDecimal(strQuantity);
				// 判断是否新增会员
				User member = this.addNewMember(organId,userName, userPhone,remarks);
				//会员不存在则新增
				User newMember = new User();
				if (member == null) {
					newMember.setAccount(sNService.gen("account", organId));
					newMember.setPassword("123456");
					newMember.setName(userName);
					newMember.setSpell(PinYinUtil.getFirstSpell(userName));
					newMember.setRemark(remarks);
					newMember.setTel(userPhone);
					newMember.setKindCode("20");
					newMember.setEmail(userEmail);
					userDao.save(newMember);
					goodsPaidDao.add(organId, newMember.getId(), goods.get(0).getId(),quantity, quantity.multiply(goods.get(0).getPrice()));
				} else {
					goodsPaidDao.add(organId, member.getId(), goods.get(0).getId(),quantity, quantity.multiply(goods.get(0).getPrice()));
				}
			}
		}
	}

	/**
	 * 解析并导入excel2007
	 * 
	 * @param fileName
	 * @param organId
	 * @param inmport_Type
	 * @return
	 * @throws IOException
	 */
	public void parseXSSFWorkbook(String fileName, String organId,String import_Type, String creator) throws IOException {
		FileInputStream fis = new FileInputStream(uploads_dir + "/" + fileName);
		XSSFWorkbook workBook = new XSSFWorkbook(fis);
		// Read the Sheet 只读取第一页签内容
		XSSFSheet sheet = workBook.getSheetAt(0);
		if (sheet == null) {
		}
		if (import_Type.equals("1")) {
			String sql = " delete from goods_paid where organ_id = '" + organId+ "'  ";
			jdbcTemplate.execute(sql);
		}
		// Read the Row 从第三行开始取参
		for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
			XSSFRow row = sheet.getRow(rowNum);
			if (row != null) {
                //参数格式化及容错处理
				String userName = this.replaceBlank(this.getValueXSSF(row.getCell(0)));// 会员名称
				String userPhone = this.replaceBlank(this.getValueXSSF(row.getCell(1)));// 电话
				String userEmail = this.replaceBlank(this.getValueXSSF(row.getCell(2)));//邮箱
				String goodsName = this.replaceBlank(this .getValueXSSF(row.getCell(3)));//商品名称
				String strQuantity = this.replaceBlank(this.getValueXSSF(row.getCell(4)));//数量
				String remarks = this.replaceBlank(this.getValueXSSF(row.getCell(5)));//备注
				//参数校验
				this.faultTolerant(userName, goodsName, strQuantity,rowNum);
				// 获取goods对象
				DetachedCriteria Cgoods = DetachedCriteria.forClass(Goods.class);
				Cgoods.add(Restrictions.eq("name", goodsName));
				Cgoods.add(Restrictions.eq("state",1));
				Cgoods.createCriteria("organ").add(Restrictions.idEq(organId));
				List<Goods> goods = goodsDao.findByCriteria(Cgoods);
				if(goods.size()<=0){
					throw new RuntimeException("该商品不存在");
				}
				//格式化数量
				BigDecimal quantity = new BigDecimal(strQuantity);
				// 判断是否新增会员
				User member = this.addNewMember(organId,userName, userPhone,remarks);
				//会员不存在则新增
				User newMember = new User();
				if (member == null||member.getUserOrganIds()!=organId) {
					newMember.setAccount(sNService.gen("account", organId));
					newMember.setPassword("123456");
					newMember.setName(userName);
					newMember.setSpell(PinYinUtil.getFirstSpell(userName));
					newMember.setRemark(remarks);
					newMember.setTel(userPhone);
					newMember.setKindCode("20");
					newMember.setEmail(userEmail);
					userDao.save(newMember);
					goodsPaidDao.add(organId, newMember.getId(), goods.get(0).getId(),quantity, quantity.multiply(goods.get(0).getPrice()));
				} else {
					goodsPaidDao.add(organId, member.getId(), goods.get(0).getId(),quantity, quantity.multiply(goods.get(0).getPrice()));
				}
			}
		}
	}

	/**
	 * 解析数据类型2007
	 * 
	 * @param xssfCell
	 * @return
	 */
	private String getValueXSSF(XSSFCell xssfCell) {
		if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfCell.getBooleanCellValue());
		} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfCell.getNumericCellValue());
		} else {
			return String.valueOf(xssfCell.getStringCellValue());
		}
	}

	/**
	 * 解析数据类型2003
	 * 
	 * @param HssfCell
	 * @return
	 */
	private String getValueHSSF(HSSFCell HssfCell) {
		if (HssfCell.getCellType() == HssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(HssfCell.getBooleanCellValue());
		} else if (HssfCell.getCellType() == HssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(HssfCell.getNumericCellValue());
		} else {
			return String.valueOf(HssfCell.getStringCellValue());
		}
	}

	/**
	 * 容错处理
	 * 
	 * @param str
	 * @return
	 */
	public String replaceBlank(String str) {
		String dest = "";
		if (str != null) {
			Pattern p = Pattern.compile("\\s*|\t|\r|\n");
			Matcher m = p.matcher(str);
			dest = m.replaceAll("");
		}
		return dest;
	}

	/**
	 * 判断用户
	 * 
	 * @param userName
	 * @param userPhone
	 * @param remarks
	 * @return
	 */
	public User addNewMember(String organId,String userName, String userPhone, String remarks) {

		DetachedCriteria Cuser = DetachedCriteria.forClass(User.class);
		Cuser.add(Restrictions.eq("state",1));		

		if (userName != null && userPhone != null && remarks == null) {
			Cuser.add(Restrictions.eq("name", userName));
			Cuser.add(Restrictions.eq("tel", userPhone));

		} else if (userName != null && userPhone == null && remarks != null) {
			Cuser.add(Restrictions.eq("name", userName));
			Cuser.add(Restrictions.eq("remark", remarks));

		} else if (userName != null && userPhone == null && remarks == null) {
			Cuser.add(Restrictions.eq("name", null));
		}
		List<User> user = userDao.findByCriteria(Cuser);
		return user.get(0);
	}
	
	/**
	 * 参数校验
	 * 
	 * @param memberName
	 * @param goodsName
	 * @param quantity
	 * @param rowNum 行数
	 */
	public void faultTolerant(String memberName,String goodsName,String quantity,int rowNum){
		
		// 容错处理（包括字符串中的空格、回车、换行符、制表符）
		Set<String> errorSet = new HashSet<String>();
		if (this.replaceBlank(memberName).equals("")|| this.replaceBlank(memberName) == null) {
			errorSet.add("会员名称格式出错，" + "位于第" + rowNum + "行!");
		}
		if (this.replaceBlank(goodsName.toString()).equals("")|| this.replaceBlank(goodsName.toString()) == null) {
			errorSet.add("商品名称格式出错，" + "位于第" + rowNum + "行!");
		}
		if (this.replaceBlank(quantity).equals("") || this.replaceBlank(quantity) == null) {
			errorSet.add("数量格式出错，" + "位于第" + rowNum + "行!");
		}
		if (errorSet.size() > 0) {
			throw new RuntimeException(StringUtils.join(errorSet, ", "));
		}

	}

}
